package smashedapples.air.sqlite
{
	import flash.data.SQLStatement;
	
	import smashedapples.air.sqlite.commands.SQLiteDeleteCommand;
	import smashedapples.air.sqlite.commands.SQLiteSaveCommand;
	
	public class SQLiteCommandBuilder
	{
		
		public static function createTableStatement( table:SQLiteTable ):String
		{
			var sql:String = "CREATE TABLE IF NOT EXISTS " + table.name + " (";

			// add column names and types
			for each( var column:SQLiteColumn in table.columns )
			{
				sql += column.name + " " + SQLiteColumnTypes.getTypeValue( column.type ) + ",";
			}
			
			// remove trailing comma, add end parenthesis
			sql = sql.slice( 0, -1 ) + ")";

			return sql;
		}
		
		
		public static function createSelectStatement( query:SQLiteQuery ):String
		{
			var sql:String = "SELECT " + query.select + " ";
				sql += 		 "FROM " + query.tableName + " ";
				sql +=		 "WHERE 1=1 ";
				
				if( query.orderBy != null )
				{
					sql += "ORDER BY " + query.orderBy;
				}
			
			return sql;
		}
		
		public static function createSaveStatement( command:SQLiteSaveCommand ):String
		{
			var sql:String;
			
			if( command.isInsert )
			{
				sql = SQLiteCommandBuilder.createInsertStatement( command );
			}
			else
			{
				sql = SQLiteCommandBuilder.createUpdateStatement( command );
			}
				
			return sql;
		}
		
		public static function createUpdateStatement( command:SQLiteSaveCommand ):String
		{
			var sql:String = "UPDATE " + command.tableName + " SET ";
			
			for( var column:String in command.parameters )
			{
				if( column != command.pkName )
					sql += column + "=" + ":" + column + ",";
			}	
			
			// remove trailing comma
			sql = sql.slice( 0, -1 );
			
			// add where statement by primary key
			sql += " WHERE " + command.pkName + "=:" + command.pkName;
			
			return sql;
		}
		
		public static function createInsertStatement(  command:SQLiteSaveCommand ):String
		{
			var sql:String = "INSERT INTO " + command.tableName + " ";
			var columnString:String = "";
			var valueString:String = "";
			
			for( var column:String in command.parameters )
			{	
				if( column != command.pkName )
				{
					columnString += column + ",";
					valueString += ":" + column + ",";
				}
			}
			// remove trailing commas and enclose in parenthesis
			columnString = "(" + columnString.slice( 0, -1 ) + ")";
			valueString = "VALUES (" + valueString.slice( 0, -1 ) + ")";
						
			sql += columnString + " " + valueString;
			
			return sql;
		}
		
		
		public static function createDeleteStatement( command:SQLiteDeleteCommand ):String
		{
			var sql:String = "DELETE FROM " + command.tableName + " WHERE 1=1 ";
			for( var column:String in command.parameters )
			{
				sql += "AND " + column + "=" + ":" + column + ",";
			}
			
			// remove trailing comma
			sql = sql.slice( 0, -1 );
			
			return sql;
		}
		
		public static function setStatementParameters( statement:SQLStatement, parameters:Object = null, pkName:String = null ):SQLStatement
		{
			if( parameters == null )
				return statement;
			
			for( var parameter:String in parameters )
			{	
				if( pkName == null  )
				{
					statement.parameters[ ":" + parameter ] = parameters[parameter];
				}
				else
				{	
					if( parameter != pkName)
						statement.parameters[ ":" + parameter ] = parameters[parameter];
				}
			} 
			
			return statement;
		}
		
	
	}
}